﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using QQTools.Data.Enums.InviteGroup;
using QQTools.Data.Models;
using QQTools.Logging;

namespace QQTools.Data.Repositories.InviteGroup
{
    public class InviteGroupRepository
    {
        private readonly string _connectionString;

        public InviteGroupRepository(string connectionString)
        {
            _connectionString = connectionString;
        }

        public async Task<List<InviteGroupModel>> GetAllAsync()
        {
            var inviteGroups = new List<InviteGroupModel>();

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("SELECT * FROM invite_group ORDER BY id ASC", connection))
                {
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            inviteGroups.Add(MapTo(reader));
                        }
                    }
                }
            }

            return inviteGroups;
        }

        public async Task<InviteGroupModel> GetByIdAsync(int id)
        {
            InviteGroupModel inviteGroup = null;

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("SELECT * FROM invite_group WHERE id = @Id ORDER BY id ASC", connection))
                {
                    command.Parameters.AddWithValue("@Id", id);
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            inviteGroup = MapTo(reader);
                        }
                    }
                }
            }

            return inviteGroup;
        }

        public async Task<List<InviteGroupModel>> GetByQQAsync(string qq)
        {
            var inviteGroups = new List<InviteGroupModel>();

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("SELECT * FROM invite_group WHERE qq = @QQ", connection))
                {
                    command.Parameters.AddWithValue("@QQ", qq);
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            inviteGroups.Add(MapTo(reader));
                        }
                    }
                }
            }

            return inviteGroups;
        }

        public async Task<List<InviteGroupModel>> GetByGroupNumberAsync(string groupNumber)
        {
            var inviteGroups = new List<InviteGroupModel>();

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("SELECT * FROM invite_group WHERE group_number = @GroupNumber ORDER BY id ASC", connection))
                {
                    command.Parameters.AddWithValue("@GroupNumber", groupNumber);
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            inviteGroups.Add(MapTo(reader));
                        }
                    }
                }
            }

            return inviteGroups;
        }

        public async Task AddAsync(InviteGroupModel inviteGroup)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    INSERT INTO invite_group (qq, group_number, invited_list_id, status)
                    VALUES (@QQ, @GroupNumber, @InvitedListId, @Status);", connection))
                {
                    command.Parameters.AddWithValue("@QQ", inviteGroup.QQ);
                    command.Parameters.AddWithValue("@GroupNumber", inviteGroup.GroupNumber);
                    command.Parameters.AddWithValue("@InvitedListId", inviteGroup.InvitedListId);
                    command.Parameters.AddWithValue("@Status", inviteGroup.Status);

                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        public async Task UpdateAsync(InviteGroupModel inviteGroup)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    UPDATE invite_group
                    SET qq = @QQ, group_number = @GroupNumber, invited_list_id = @InvitedListId, status = @Status, update_time = CURRENT_TIMESTAMP
                    WHERE id = @Id;", connection))
                {
                    command.Parameters.AddWithValue("@Id", inviteGroup.Id);
                    command.Parameters.AddWithValue("@QQ", inviteGroup.QQ);
                    command.Parameters.AddWithValue("@GroupNumber", inviteGroup.GroupNumber);
                    command.Parameters.AddWithValue("@InvitedListId", inviteGroup.InvitedListId);
                    command.Parameters.AddWithValue("@Status", inviteGroup.Status);

                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        public async Task DeleteAsync(int id)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("DELETE FROM invite_group WHERE id = @Id;", connection))
                {
                    command.Parameters.AddWithValue("@Id", id);
                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        public async Task ClearTableAsync()
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("DELETE FROM invite_group;", connection))
                {
                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        /// <summary>
        /// 获取下一个需要处理的记录，并锁定该记录
        /// </summary>
        /// <returns></returns>
        public async Task<InviteGroupModel> GetAndLockNextInviteGroupAsync()
        {
            InviteGroupModel inviteGroup = null;

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        // 获取 status 为 1 的记录，并按 id 升序排列
                        using (var command = new SQLiteCommand(@"
                            SELECT * FROM invite_group
                            WHERE status = 1
                            ORDER BY id ASC
                            LIMIT 1;", connection, transaction))
                        {
                            using (var reader = await command.ExecuteReaderAsync())
                            {
                                if (await reader.ReadAsync())
                                {
                                    inviteGroup = MapTo(reader);
                                }
                            }
                        }

                        if (inviteGroup != null)
                        {
                            // 更新 status 为 3
                            using (var updateCommand = new SQLiteCommand(@"
                                UPDATE invite_group
                                SET status = 3, update_time = CURRENT_TIMESTAMP
                                WHERE id = @Id;", connection, transaction))
                            {
                                updateCommand.Parameters.AddWithValue("@Id", inviteGroup.Id);
                                await updateCommand.ExecuteNonQueryAsync();
                            }
                        }

                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        Console.WriteLine($"Transaction failed: {ex.Message}");
                    }
                }
            }

            return inviteGroup;
        }

        /// <summary>
        /// 根据QQ获取下一个需要处理的记录，并锁定该记录
        /// </summary>
        /// <param name="qq">QQ</param>
        /// <returns></returns>
        public async Task<InviteGroupModel> GetAndLockNextInviteGroupAsync(string qq)
        {
            InviteGroupModel inviteGroup = null;

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        // 获取 status 为 1 且 qq 匹配的记录，并按 id 升序排列
                        using (var command = new SQLiteCommand(@"
                    SELECT * FROM invite_group
                    WHERE status = 1 AND qq = @QQ
                    ORDER BY id ASC
                    LIMIT 1;", connection, transaction))
                        {
                            command.Parameters.AddWithValue("@QQ", qq);
                            using (var reader = await command.ExecuteReaderAsync())
                            {
                                if (await reader.ReadAsync())
                                {
                                    inviteGroup = MapTo(reader);
                                }
                            }
                        }

                        if (inviteGroup != null)
                        {
                            // 更新 status 为 3
                            using (var updateCommand = new SQLiteCommand(@"
                        UPDATE invite_group
                        SET status = 3, update_time = CURRENT_TIMESTAMP
                        WHERE id = @Id;", connection, transaction))
                            {
                                updateCommand.Parameters.AddWithValue("@Id", inviteGroup.Id);
                                await updateCommand.ExecuteNonQueryAsync();
                            }
                        }

                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        Console.WriteLine($"Transaction failed: {ex.Message}");
                    }
                }
            }

            return inviteGroup;
        }

        public async Task UpdateAsync(InviteGroupModel inviteGroup, bool updateStatus = true)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                string updateCommandText = @"
            UPDATE invite_group
            SET qq = @QQ, group_number = @GroupNumber, invited_list_id = @InvitedListId, update_time = CURRENT_TIMESTAMP";
                if (updateStatus)
                {
                    updateCommandText += ", status = @Status";
                }
                updateCommandText += " WHERE id = @Id;";

                using (var command = new SQLiteCommand(updateCommandText, connection))
                {
                    command.Parameters.AddWithValue("@Id", inviteGroup.Id);
                    command.Parameters.AddWithValue("@QQ", inviteGroup.QQ);
                    command.Parameters.AddWithValue("@GroupNumber", inviteGroup.GroupNumber);
                    command.Parameters.AddWithValue("@InvitedListId", inviteGroup.InvitedListId);
                    if (updateStatus)
                    {
                        command.Parameters.AddWithValue("@Status", inviteGroup.Status);
                    }

                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        /// <summary>
        /// 如果 qq 和 group_number 都存在则更新数据，否则插入新数据
        /// </summary>
        /// <param name="inviteGroup"></param>
        /// <returns></returns>
        public async Task AddOrUpdateAsync(InviteGroupModel inviteGroup)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();

                // 检查 qq 和 group_number 是否已经存在
                using (var checkCommand = new SQLiteCommand(@"
            SELECT id FROM invite_group
            WHERE qq = @QQ
            AND group_number = @GroupNumber 
            ORDER BY id ASC", connection))
                {
                    checkCommand.Parameters.AddWithValue("@QQ", inviteGroup.QQ);
                    checkCommand.Parameters.AddWithValue("@GroupNumber", inviteGroup.GroupNumber);
                    var existingId = await checkCommand.ExecuteScalarAsync();

                    if (existingId != null)
                    {
                        // 如果存在，则更新数据, 但是不更新 status
                        int id = Convert.ToInt32(existingId);
                        inviteGroup.Id = id;
                        await UpdateAsync(inviteGroup, updateStatus: false);
                    }
                    else
                    {
                        // 如果不存在，则插入新数据
                        await AddAsync(inviteGroup);
                    }
                }
            }
        }

        /// <summary>
        /// 将所有状态为 3 的记录重置为 1
        /// </summary>
        /// <returns></returns>
        public async Task ResetStatusToPendingAsync()
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    UPDATE invite_group
                    SET status = 1, update_time = CURRENT_TIMESTAMP
                    WHERE status = 3;", connection))
                {
                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        /// <summary>
        /// 根据 qq 和 group_number 修改 status 的值
        /// </summary>
        /// <param name="qq">QQ</param>
        /// <param name="groupNumber">群号</param>
        /// <param name="status">状态</param>
        /// <returns></returns>
        public async Task UpdateStatusByQQAndGroupNumberAsync(string qq, string groupNumber, InviteGroupStatus status)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    UPDATE invite_group
                    SET status = @Status, update_time = CURRENT_TIMESTAMP
                    WHERE qq = @QQ AND group_number = @GroupNumber;", connection))
                {
                    command.Parameters.AddWithValue("@QQ", qq);
                    command.Parameters.AddWithValue("@GroupNumber", groupNumber);
                    command.Parameters.AddWithValue("@Status", (int)status);

                    await command.ExecuteNonQueryAsync();
                }
            }
        }


        /// <summary>
        /// 根据 qq  和 status 查询记录
        /// </summary>
        /// <param name="qq"></param>
        /// <param name="groupNumber"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public async Task<List<InviteGroupModel>> GetByQQAndStatusAsync(string qq, InviteGroupStatus status)
        {
            var inviteGroups = new List<InviteGroupModel>();

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    SELECT * FROM invite_group
                    WHERE qq = @QQ
                    AND status = @Status;", connection))
                {
                    command.Parameters.AddWithValue("@QQ", qq);
                    command.Parameters.AddWithValue("@Status", (int)status);

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            inviteGroups.Add(MapTo(reader));
                        }
                    }
                }
            }

            return inviteGroups;
        }


        /// <summary>
        /// 根据 qq 和 status 查询记录数量
        /// </summary>
        /// <param name="qq"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public async Task<int> GetCountByQQAndStatusAsync(string qq, InviteGroupStatus status)
        {
            int count = 0;

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    SELECT COUNT(*) FROM invite_group
                    WHERE qq = @QQ
                    AND status = @Status;", connection))
                {
                    command.Parameters.AddWithValue("@QQ", qq);
                    command.Parameters.AddWithValue("@Status", (int)status);

                    count = Convert.ToInt32(await command.ExecuteScalarAsync());
                }
            }

            return count;
        }


        /// <summary>
        /// 根据QQ获取记录总数
        /// </summary>
        /// <param name="qq"></param>
        /// <returns></returns>
        public async Task<int> GetCountByQQAsync(string qq)
        {
            int count = 0;
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    SELECT COUNT(*) FROM invite_group
                    WHERE qq = @QQ;", connection))
                {
                    command.Parameters.AddWithValue("@QQ", qq);
                    count = Convert.ToInt32(await command.ExecuteScalarAsync());
                }
            }
            return count;
        }

        /// <summary>
        /// 根据QQ获取status为InviteGroupStatus.Success的总记录数
        /// </summary>
        /// <param name="qq"></param>
        public async Task<int> GetSuccessCountByQQAsync(string qq)
        {
            int count = 0;
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    SELECT COUNT(*) FROM invite_group
                    WHERE qq = @QQ
                    AND status = @Status;", connection))
                {
                    command.Parameters.AddWithValue("@QQ", qq);
                    command.Parameters.AddWithValue("@Status", (int)InviteGroupStatus.Success);
                    count = Convert.ToInt32(await command.ExecuteScalarAsync());
                }
            }
            return count;
        }


        /// <summary>
        /// 批量插入记录：如果记录已存在（基于 qq 和 group_number），则跳过
        /// </summary>
        /// <param name="models">待插入的 InviteGroupModel 集合</param>
        public async Task BulkInsertAsync(List<InviteGroupModel> models)
        {
            if (models == null || models.Count == 0)
            {
                LogManager.Info("BulkInsertAsync：传入的记录集合为空，无需插入。");
                return;
            }

            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    using (var transaction = connection.BeginTransaction())
                    {
                        foreach (var model in models)
                        {
                            // 使用 INSERT ... SELECT 结合 WHERE NOT EXISTS 判断记录是否存在
                            string sql = @"
                                INSERT INTO invite_group 
                                (qq, group_number, invited_list_id, status, create_time, update_time)
                                SELECT @qq, @group_number, @invited_list_id, @status, @create_time, @update_time
                                WHERE NOT EXISTS (
                                    SELECT 1 FROM invite_group WHERE qq = @qq AND group_number = @group_number
                             );";

                            using (var command = new SQLiteCommand(sql, connection, transaction))
                            {
                                command.Parameters.AddWithValue("@qq", model.QQ);
                                command.Parameters.AddWithValue("@group_number", model.GroupNumber);
                                command.Parameters.AddWithValue("@invited_list_id", model.InvitedListId);
                                command.Parameters.AddWithValue("@status", (int)model.Status);
                                command.Parameters.AddWithValue("@create_time", model.CreateTime);
                                command.Parameters.AddWithValue("@update_time", model.UpdateTime);

                                await command.ExecuteNonQueryAsync();
                            }
                        }
                        transaction.Commit();
                    }
                }
                LogManager.Info("BulkInsertAsync 成功批量插入记录（存在重复记录时跳过）。");
            }
            catch (Exception ex)
            {
                LogManager.Error("BulkInsertAsync 异常：" + ex);

            }
        }


        /// <summary>
        /// 将 SqlDataReader 映射到 InviteGroupModel 对象
        /// </summary>
        private InviteGroupModel MapTo(System.Data.Common.DbDataReader reader)
        {
            return new InviteGroupModel
            {
                Id = reader.GetInt32(reader.GetOrdinal("id")),
                QQ = reader.IsDBNull(reader.GetOrdinal("qq")) ? null : reader.GetString(reader.GetOrdinal("qq")),
                GroupNumber = reader.IsDBNull(reader.GetOrdinal("group_number")) ? null : reader.GetString(reader.GetOrdinal("group_number")),
                InvitedListId = reader.IsDBNull(reader.GetOrdinal("invited_list_id")) ? 0 : reader.GetInt32(reader.GetOrdinal("invited_list_id")),
                Status = reader.IsDBNull(reader.GetOrdinal("status")) ? InviteGroupStatus.Pending : (InviteGroupStatus)reader.GetInt32(reader.GetOrdinal("status")),
                CreateTime = reader.IsDBNull(reader.GetOrdinal("create_time")) ? DateTime.MinValue : reader.GetDateTime(reader.GetOrdinal("create_time")),
                UpdateTime = reader.IsDBNull(reader.GetOrdinal("update_time")) ? DateTime.MinValue : reader.GetDateTime(reader.GetOrdinal("update_time"))
            };
        }
    }
}